Db2 Macros

The %sql command also allows the use of macros. Macros are used to substitute text into SQL commands that you execute. Macros substitution is done before any SQL is executed. This allows you to create macros that include commonly used SQL commands or parameters rather than having to type them in. Before using any macros, we must make sure we have loaded the Db2 extensions.



In [ ]:

    
%run db2.ipynb

Macro Basics

A Macro command begins with a percent sign (% similar to the %sql magic command) and can be found anywhere within a %sql line or %%sql block. Macros must be separated from other text in the SQL with a space.

To define a macro, the %%sql macro <name> command is used. The body of the macro is found in the cell below the definition of the macro. This simple macro called EMPTABLE will substitute a SELECT statement into a SQL block.



In [ ]:

    
%%sql macro emptable
select * from employee

The name of the macro follows the %%sql macro command and is case sensitive. To use the macro, we can place it anywhere in the %sql block. This first example uses it by itself.



In [ ]:

    
%sql %emptable

The actual SQL that is generated is not shown by default. If you do want to see the SQL that gets generated, you can use the -e (echo) option to display the final SQL statement. The following example will display the generated SQL. Note that the echo setting is only used to display results for the current cell that is executing.



In [ ]:

    
%%sql -e
%emptable

Since we can use the %emptable anywhere in our SQL, we can add additional commands around it. In this example we add some logic to the select statement.



In [ ]:

    
%%sql
%emptable
where empno = '000010'

Macros can also have parameters supplied to them. The parameters are included after the name of the macro. Here is a simple macro which will use the first parameter as the name of the column we want returned from the EMPLOYEE table.



In [ ]:

    
%%sql macro emptable
SELECT {1} FROM EMPLOYEE

This example illustrates two concepts. The MACRO command will replace any existing macro with the same name. Since we already have an emptable macro, the macro body will be replaced with this code. In addition, macros only exist for the duration of your notebook. If you create another Jupyter notebook, it will not contain any macros that you may have created. If there are macros that you want to share across notebooks, you should create a separate notebook and place all of the macro definitions in there. Then you can include these macros by executing the %run command using the name of the notebook that contains the macros.

The following SQL shows the use of the macro with parameters.



In [ ]:

    
%%sql
%emptable(lastname)

The remainder of this notebook will explore the advanced features of macros.

Macro Parameters

Macros can have up to 9 parameters supplied to them. The parameters are numbered from 1 to 9, left to right in the argument list for the macro. For instance, the following macro has 5 paramters:

%emptable(lastname,firstnme,salary,bonus,'000010')

Parameters are separated by commas, and can contain strings as shown using single or double quotes. When the parameters are used within a macro, the quotes are not included as part of the string. If you do want to pass the quotes as part of the parameter, use square brackets [] around the string. For instance, the following parameter will not have quotes passed to the macro:

%sql %abc('no quotes')

To send the string with quotes, you could surround the parameter with other quotes "'hello'" or use the following technique if you use multiple quotes in your string:

%sql %abc (['quotes'])

To use a parameter within your macro, you enclose the parameter number with braces {}. The next command will illustrate the use of the five parameters.



In [ ]:

    
%%sql macro emptable
display on
SELECT {1},{2},{3},{4} 
FROM EMPLOYEE
WHERE EMPNO = '{5}'

Note that the EMPNO field is a character field in the EMPLOYEE table. Even though the employee number was supplied as a string, the quotes are not included in the parameter. The macro places quotes around the parameter {5} so that it is properly used in the SQL statement. The other feature of this macro is that the display (on) command is part of the macro body so the generated SQL will always be displayed.



In [ ]:

    
%sql %emptable(lastname,firstnme,salary,bonus,'000010')

We can modify the macro to assume that the parameters will include the quotes in the string.



In [ ]:

    
%%sql macro emptable
SELECT {1},{2},{3},{4} 
FROM EMPLOYEE
WHERE EMPNO = {5}

We just have to make sure that the quotes are part of the parameter now.



In [ ]:

    
%sql -e %emptable(lastname,firstnme,salary,bonus,"'000010'")

We could use the square brackets as an alternative way of passing the parameter.



In [ ]:

    
%sql -e %emptable(lastname,firstnme,salary,bonus,['000010'])

Parameters can also be named in a macro. To name an input value, the macro needs to use the format:

field=value

For instance, the following macro call will have 2 numbered parameters and one named parameter:

%showemp(firstnme,lastname,logic="WHERE EMPNO='000010'")

From within the macro the parameter count would be 2 and the value for parameter 1 is firstnme, and the value for parameter 2 is lastname. Since we have a named parameter, it is not included in the list of numbered parameters. In fact, the following statement is equivalent since unnamed parameters are numbered in the order that they are found in the macro, ignoring any named parameters that are found:

%showemp(firstnme,logic="WHERE EMPNO='000010'",lastname)

The following macro illustrates this feature.



In [ ]:

    
%%sql macro showemp
SELECT {1},{2} FROM EMPLOYEE
  {logic}



In [ ]:

    
%sql %showemp(firstnme,lastname,logic="WHERE EMPNO='000010'")



In [ ]:

    
%sql %showemp(firstnme,logic="WHERE EMPNO='000010'",lastname)

Named parameters are useful when there are many options within the macro and you don't want to keep track of which position it is in. In addition, if you have a variable number of parameters, you should use named parameters for the fixed (required) parameters and numbered parameters for the optional ones.

Macro Coding Overview

Macros can contain any type of text, including SQL commands. In addition to the text, macros can also contain the following keywords:

echo - Display a message
exit - Exit the macro immediately
if/else/endif - Conditional logic
var - Set a variable
display - Turn the display of the final text on

The only restriction with macros is that macros cannot be nested. This means I can't call a macro from within a macro. The sections below explain the use of each of these statement types.

Echo Option

The -e option will result in the final SQL being display after the macro substitution is done.

%%sql -e
%showemp(...)



In [ ]:

    
%%sql macro showdisplay
SELECT * FROM EMPLOYEE FETCH FIRST ROW ONLY

Using the -e flag will display the final SQL that is run.



In [ ]:

    
%sql -e %showdisplay

If we remove the -e option, the final SQL will not be shown.



In [ ]:

    
%sql %showdisplay

Exit Command

The exit command will terminate the processing within a macro and not run the generated SQL. You would use this when a condition is not met within the macro (like a missing parameter).



In [ ]:

    
%%sql macro showexit
echo This message gets shown
SELECT * FROM EMPLOYEE FETCH FIRST ROW ONLY
exit
echo This message does not get shown

The macro that was defined will not show the second statement, nor will it execute the SQL that was defined in the macro body.



In [ ]:

    
%sql %showexit

Echo Command

As you already noticed in the previous example, the echo command will display information on the screen. Any text following the command will have variables substituted and then displayed with a green box surrounding it. The following code illustates the use of the command.



In [ ]:

    
%%sql macro showecho
echo Here is a message
echo Two lines are shown

The echo command will show each line as a separate box.



In [ ]:

    
%sql %showecho

If you want to have a message go across multiple lines use the <br> to start a new line.



In [ ]:

    
%%sql macro showecho
echo Here is a paragraph. <br> And a final paragraph.



In [ ]:

    
%sql %showecho

Var Command

The var (variable) command sets a macro variable to a value. A variable is referred to in the macro script using curly braces {name}. By default the arguments that are used in the macro call are assigned the variable names {1} to {9}. If you use a named argument (option="value") in the macro call, a variable called {option} will contain the value within the macro.

To set a variable within a macro you would use the var command:

var name value

The variable name can be any name as long as it only includes letters, numbers, underscore _ and $. Variable names are case sensitive so {a} and {A} are different. When the macro finishes executing, the contents of the variables will be lost. If you do want to keep a variable between macros, you should start the name of the variable with a $ sign:

var $name value

This variable will persist between macro calls.



In [ ]:

    
%%sql macro initialize
var $hello Hello There 
var hello You won't see this



In [ ]:

    
%%sql macro runit
echo The value of hello is *{hello}*
echo {$hello}

Calling runit will display the variable that was set in the first macro.



In [ ]:

    
%sql %initialize
%sql %runit

A variable can be converted to uppercase by placing the ^ beside the variable name or number.



In [ ]:

    
%%sql macro runit
echo The first parameter is {^1}



In [ ]:

    
%sql %runit(Hello There)

The string following the variable name can include quotes and these will not be removed. Only quotes that are supplied in a parameter to a macro will have the quotes removed.



In [ ]:

    
%%sql macro runit
var hello This is a long string without quotes
var hello2 'This is a long string with quotes'
echo {hello} <br> {hello2}



In [ ]:

    
%sql %runit

When passing parameters to a macro, the program will automatically create variables based on whether they are positional parameters (1, 2, ..., n) or named parameters. The following macro will be used to show how parameters are passed to the routine.



In [ ]:

    
%%sql macro showvar
echo parm1={1} <br>parm2={2} <br>message={message}

Calling the macro will show how the variable names get assigned and used.



In [ ]:

    
%sql %showvar(parameter 1, another parameter,message="Hello World")

If you pass an empty value (or if a variable does not exist), a "null" value will be shown.



In [ ]:

    
%sql %showvar(1,,message="Hello World")

An empty string also returns a null value.



In [ ]:

    
%sql %showvar(1,2,message="")

Finally, any string that is supplied to the macro will not include the quotes in the variable. The Hello World string will not have quotes when it is displayed:



In [ ]:

    
%sql %showvar(1,2,message="Hello World")

You need to supply the quotes in the script or macro when using variables since quotes are stripped from any strings that are supplied.



In [ ]:

    
%%sql macro showvar
echo parm1={1} <br>parm2={2} <br>message='{message}'



In [ ]:

    
%sql %showvar(1,2,message="Hello World")

The count of the total number of parameters passed is found in the {argc} variable. You can use this variable to decide whether or not the user has supplied the proper number of arguments or change which code should be executed.



In [ ]:

    
%%sql macro showvar
echo The number of unnamed parameters is {argc}. The where clause is *{where}*.

Unnamed parameters are included in the count of arguments while named parameters are ignored.



In [ ]:

    
%sql %showvar(1,2,option=nothing,3,4,where=)

If/Else/Endif Command

If you need to add conditional logic to your macro then you should use the if/else/endif commands. The format of the if statement is:

if variable condition value
   statements
else
   statements
endif

The else portion is optional, but the block must be closed with the endif command. If statements can be nested up to 9 levels deep:

if condition 1
   if condition 2
      statements
   else
      if condition 3
         statements
      end if 
   endif
endif

If the condition in the if clause is true, then anything following the if statement will be executed and included in the final SQL statement. For instance, the following code will create a SQL statement based on the value of parameter 1:

if {1} = null
   SELECT * FROM EMPLOYEE
else
   SELECT {1} FROM EMPLOYEE
endif

Conditions

The if statement requires a condition to determine whether or not the block should be executed. The condition uses the following format:

if {variable} condition {variable} | constant | null

Variable can be a number from 1 to 9 which represents the argument in the macro list. So {1} refers to the first argument. The variable can also be the name of a named parameter or global variable.

The condition is one of the following comparison operators:

=, ==: Equal to
<: Less than
>: Greater than
<=,=<: Less than or equal to
>=, =>: Greater than or equal to
!=, <> : Not equal to

The variable or constant will have quotes stripped away before doing the comparison. If you are testing for the existence of a variable, or to check if a variable is empty, use the keyword null.



In [ ]:

    
%%sql macro showif
if {argc} = 0
   echo No parameters supplied
   if {option} <> null
      echo The optional parameter option was set: {option}
   endif
else
   if {argc} = "1"
       echo One parameter was supplied
   else
       echo More than one parameter was supplied: {argc}
   endif
endif

Running the previous macro with no parameters will check to see if the option keyword was used.



In [ ]:

    
%sql %showif

Now include the optional parameter.



In [ ]:

    
%sql %showif(option="Yes there is an option")

Finally, issue the macro with multiple parameters.



In [ ]:

    
%sql %showif(Here,are,a,number,of,parameters)

One additional option is available for variable substitution. If the first character of the variable name or parameter number is the ^ symbol, it will uppercase the entire string.



In [ ]:

    
%%sql macro showif
if {option} <> null
   echo The optional parameter option was set: {^option}
endif



In [ ]:

    
%sql %showif(option="Yes there is an option")